package viettravel.DTO;

import java.io.IOException;
import java.util.ArrayList;

import viettravel.DAO.ActivityDAO;
import viettravel.DAO.PlaceDAO;
import viettravel.DAO.ProvinceDAO;
import viettravel.DAO.RegionDAO;
import viettravel.DAO.SpecialtyDAO;
import viettravel.DAO.TouristFormDAO;
import viettravel.common.Constants;
import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class ViettravelDTO {
	private static Context mContext;
	private DatabaseConnection mDBConnection;
	public SQLiteDatabase mViettravelDB;

	private static ViettravelDTO mViettravelDTO = null;

	public static ViettravelDTO getInstance() {
		if (mViettravelDTO == null)
			mViettravelDTO = new ViettravelDTO();
		return mViettravelDTO;
	}

	private ViettravelDTO() {
		Log.v(Constants.TAG, "test");
		mContext = Constants.CONTEXT;
		mDBConnection = new DatabaseConnection(mContext);
		try {
			mDBConnection.createDataBase();
		} catch (IOException e) {
			e.printStackTrace();
		}
		mDBConnection.openDataBase();
		mViettravelDB = mDBConnection.getDB();
	}

	/*
	 * Regions
	 */
	public RegionDAO findRegionById(int _regionId) {
		RegionDAO result = null;
		String findRegionByIdQuery = String.format(
				Constants.QUERY_GET_REGION_BY_ID, _regionId);
		// Cursor cursor = mViettravelDB.query("REGIONS", null, null, null,
		// null, null, null);
		Cursor cursor = mViettravelDB.rawQuery(findRegionByIdQuery, null);
		if (cursor.moveToFirst()) {
			result = new RegionDAO(_regionId, cursor.getString(1),
					cursor.getString(2));
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public ArrayList<RegionDAO> findAllRegion() {
		ArrayList<RegionDAO> result = new ArrayList<RegionDAO>();
		String findRegionByIdQuery = Constants.QUERY_GET_ALL_REGIONS;
		Cursor cursor = mViettravelDB.rawQuery(findRegionByIdQuery, null);
		if (cursor.moveToFirst()) {
			do {
				result.add(new RegionDAO(cursor.getInt(0), cursor.getString(1),
						cursor.getString(2)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public void insertRegion(int id, String name, String description) {
		try {

			/*
			 * mViettravelDB .execSQL(
			 * "INSERT OR REPLACE INTO REGIONS (Region_Id ,Region_Name, Region_Description) VALUES ("
			 * + id + ",'" + name + "','" + description + "')");
			 */
			String insertRegionQuery = String.format(
					Constants.QUERY_INSERT_REGIONS, id, name, description);
			mViettravelDB.execSQL(insertRegionQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	/*
	 * Province
	 */

	public void insertProvince(int id, String name, String description,
			int regionId) {
		try {

			/*
			 * mViettravelDB .execSQL(
			 * "INSERT OR REPLACE INTO PROVINCES (Province_Id ,Province_Name, Province_Description, Region_Id) VALUES ("
			 * + id + ",'" + name + "','" + description + "',"+regionId+")");
			 */
			String insertProvinceQuery = String.format(
					Constants.QUERY_INSERT_PROVINCES, id, name, description,
					regionId);
			mViettravelDB.execSQL(insertProvinceQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	public ProvinceDAO findProvinceById(int _provinceId) {
		ProvinceDAO result = null;
		String findProvinceByIdQuery = String.format(
				Constants.QUERY_GET_PROVINCE_BY_ID, _provinceId);
		Cursor cursor = mViettravelDB.rawQuery(findProvinceByIdQuery, null);
		if (cursor.moveToFirst()) {
			result = new ProvinceDAO(_provinceId, cursor.getString(1),
					cursor.getString(2));
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public ArrayList<ProvinceDAO> findProvinceByRegionId(int _regionId) {
		ArrayList<ProvinceDAO> result = new ArrayList<ProvinceDAO>();
		String findProvinceByRegionIdQuery = String.format(
				Constants.QUERY_GET_PROVINCES_BY_REGION_ID, _regionId);
		Cursor cursor = mViettravelDB.rawQuery(findProvinceByRegionIdQuery,
				null);
		if (cursor.moveToFirst()) {
			do {
				result.add(new ProvinceDAO(cursor.getInt(0), cursor
						.getString(1), cursor.getString(2)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	// PLACE
	public PlaceDAO findPlaceById(int _placeId) {
		PlaceDAO result = null;
		String findPlaceByIdQuery = String.format(
				Constants.QUERY_GET_PLACE_BY_ID, _placeId);
		Cursor cursor = mViettravelDB.rawQuery(findPlaceByIdQuery, null);
		if (cursor.moveToFirst()) {
			result = new PlaceDAO(_placeId, cursor.getString(1),
					cursor.getString(2), cursor.getFloat(4),
					cursor.getFloat(3), cursor.getFloat(5));
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		Log.v(Constants.TAG + "get",
				result.getPlaceLat() + "," + result.getPlaceLong());
		return result;
	}

	public ArrayList<PlaceDAO> findPlaceByProvinceId(int _provinceId) {
		ArrayList<PlaceDAO> result = new ArrayList<PlaceDAO>();
		String findPlaceByProvinceIdQuery = String.format(
				Constants.QUERY_GET_PLACES_BY_PROVINCE_ID, _provinceId);
		Cursor cursor = mViettravelDB
				.rawQuery(findPlaceByProvinceIdQuery, null);
		if (cursor.moveToFirst()) {
			do {
				result.add(new PlaceDAO(cursor.getInt(0), cursor.getString(1),
						cursor.getString(2), cursor.getFloat(4), cursor
								.getFloat(3), cursor.getFloat(5)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public ArrayList<PlaceDAO> findAllPlace() {
		ArrayList<PlaceDAO> result = new ArrayList<PlaceDAO>();
		String findPlaceByProvinceIdQuery = String
				.format(Constants.QUERY_GET_ALL_PLACE);
		Cursor cursor = mViettravelDB
				.rawQuery(findPlaceByProvinceIdQuery, null);
		if (cursor.moveToFirst()) {
			do {
				result.add(new PlaceDAO(cursor.getInt(0), cursor.getString(1),
						cursor.getString(2), cursor.getFloat(4), cursor
								.getFloat(3), cursor.getFloat(5)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public ArrayList<PlaceDAO> findPlaceSearch(String placeName,
			ArrayList<Integer> specialtyList, ArrayList<Integer> activityList,
			ArrayList<Integer> listTouristForm) {
		ArrayList<PlaceDAO> result = new ArrayList<PlaceDAO>();
		String placeQuery = Constants.QUERY_SEARCH_PLACE
				+ " AND ( %s ) AND ( %s ) AND ( %s ) " + "GROUP BY P.Place_Id";

		String querySpecialty = " P.Place_Id = PS.Place_Id AND ( 0 = 1 ";
		String queryTour = " P.Place_Id = PT.Place_Id AND (0 = 1 ";
		String queryActivity = " P.Place_Id = PA.Place_Id AND (0 = 1 ";

		if (specialtyList.size() == 0)
			querySpecialty = " 1 = 1 ";
		else {
			for (int i = 0; i < specialtyList.size(); i++) {
				querySpecialty += String.format(" OR PS.Specialty_Id = %s ",
						specialtyList.get(i));
			}
			querySpecialty += ")";
		}
		if (activityList.size() == 0)
			queryActivity = " 1 = 1 ";
		else {
			for (int i = 0; i < activityList.size(); i++) {
				queryActivity += String.format(" OR PA.Activity_Id = %s ",
						activityList.get(i));
			}
			queryActivity += ")";
		}
		if (listTouristForm.size() == 0)
			queryTour = " 1 = 1 ";
		else {
			for (int i = 0; i < listTouristForm.size(); i++) {
				queryTour += String.format(" OR PT.Tour_Id = %s ",
						listTouristForm.get(i));
			}
			queryTour += ")";
		}

		String findPlaceQuery = String.format(placeQuery, queryActivity,
				querySpecialty, queryTour);
		Log.v(Constants.TAG + "QUERY", findPlaceQuery);
		Cursor cursor = mViettravelDB.rawQuery(findPlaceQuery, null);

		if (cursor.moveToFirst()) {
			do {
				result.add(new PlaceDAO(cursor.getInt(0), cursor.getString(1),
						cursor.getString(2), cursor.getFloat(4), cursor
								.getFloat(3), cursor.getFloat(5)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public void insertPlace(int id, String name, String description, Float lat,
			Float lng, Float rating, int provinceId) {
		try {
			String insertPlaceQuery = String.format(
					Constants.QUERY_INSERT_PLACES, id, name, description, lat,
					lng, rating, provinceId);
			mViettravelDB.execSQL(insertPlaceQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	// FAVORIT
	@SuppressLint("UseValueOf")
	public ArrayList<Integer> findFavorits() {
		ArrayList<Integer> result = new ArrayList<Integer>();
		String findFavoritsQuery = String.format(Constants.QUERY_GET_FAVORITS);
		Cursor cursor = mViettravelDB.rawQuery(findFavoritsQuery, null);
		if (cursor.moveToFirst()) {
			do {
				result.add(new Integer(cursor.getInt(0)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public void insertFavotite(int id) {
		try {
			String insertFavoriteQuery = String.format(
					Constants.QUERY_INSERT_FAVOURITE, id);
			mViettravelDB.execSQL(insertFavoriteQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	// TOURIST FORM
	public ArrayList<TouristFormDAO> findAllTourists() {
		ArrayList<TouristFormDAO> result = new ArrayList<TouristFormDAO>();
		String findAllTourist = String.format(Constants.QUERY_GET_TOURISTS);
		Cursor cursor = mViettravelDB.rawQuery(findAllTourist, null);
		if (cursor.moveToFirst()) {
			do {
				result.add(new TouristFormDAO(cursor.getInt(0), cursor
						.getString(1), cursor.getString(2)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public void insertTourist(int id, String name, String description) {
		try {
			String insertTouristQuery = String.format(
					Constants.QUERY_INSERT_TOURISTS, id, name, description);
			mViettravelDB.execSQL(insertTouristQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	// ACTIVITY
	public ArrayList<ActivityDAO> findAllActivities() {
		ArrayList<ActivityDAO> result = new ArrayList<ActivityDAO>();
		String findAllActivities = String
				.format(Constants.QUERY_GET_ACTIVITIES);
		Cursor cursor = mViettravelDB.rawQuery(findAllActivities, null);
		if (cursor.moveToFirst()) {
			do {
				result.add(new ActivityDAO(cursor.getInt(0), cursor
						.getString(1), cursor.getString(2)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public void insertActivitiy(int id, String name, String description) {
		try {
			String insertActivitiyQuery = String.format(
					Constants.QUERY_INSERT_ACTIVITY, id, name, description);
			mViettravelDB.execSQL(insertActivitiyQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	// SPECIALTY
	public ArrayList<SpecialtyDAO> findAllSpecialties() {
		ArrayList<SpecialtyDAO> result = new ArrayList<SpecialtyDAO>();
		String findAllSpecialties = String
				.format(Constants.QUERY_GET_SPECIALTIES);
		Cursor cursor = mViettravelDB.rawQuery(findAllSpecialties, null);
		if (cursor.moveToFirst()) {
			do {
				result.add(new SpecialtyDAO(cursor.getInt(0), cursor
						.getString(1), cursor.getString(2)));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return result;
	}

	public void insertSpecialty(int id, String name, String description) {
		try {
			String insertSpecialtyQuery = String.format(
					Constants.QUERY_INSERT_SPECIALTY, id, name, description);
			mViettravelDB.execSQL(insertSpecialtyQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	public void insertPlace_Activity(int placeId, int activityId) {
		try {
			String insertPlaceActivityQuery = String.format(
					Constants.QUERY_INSERT_PLACE_ACTIVITY, placeId, activityId);
			mViettravelDB.execSQL(insertPlaceActivityQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	public void insertPlace_Specialty(int placeId, int specialtyId) {
		try {
			String insertPlaceActivityQuery = String.format(
					Constants.QUERY_INSERT_PLACE_SPECIALTY, placeId,
					specialtyId);
			mViettravelDB.execSQL(insertPlaceActivityQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}

	public void insertPlace_Tour(int placeId, int tourId) {
		try {
			String insertPlaceActivityQuery = String.format(
					Constants.QUERY_INSERT_PLACE_TOUR, placeId, tourId);
			mViettravelDB.execSQL(insertPlaceActivityQuery);
		} catch (Exception e) {
			Log.e("Write Database", e.getMessage());
		}

	}
}
